package com.css.fxfzdzzh.modules.reportManagement.repository;

import com.alibaba.fastjson.JSONObject;
import com.css.fxfzdzzh.constants.FxfzConstants;
import com.css.fxfzdzzh.constants.YNEnum;

import com.css.fxfzdzzh.modules.reportManagement.entity.MultipleReport;
import com.css.fxfzdzzh.modules.reportManagement.entity.Report;
import com.css.fxfzdzzh.modules.reportManagement.param.MultiplePageQueryParam;
import com.css.fxfzdzzh.modules.reportManagement.vo.MultipleReportVO;
import com.css.fxfzdzzh.modules.taskManage.entity.TaskEntity;
import com.css.fxfzdzzh.util.CreateGson;
import com.css.fxfzdzzh.util.PlatformObjectUtils;
import com.css.fxfzdzzh.web.PlatformSessionContext;
import com.css.fxfzdzzh.zcpt.sys.entity.SUser;
import com.css.fxfzdzzh.zcpt.sys.service.SUserService;
import com.css.fxfzdzzh.zcpt.sys.service.SysAreaService;
import com.css.fxfzdzzh.zcpt.sys.service.SysRoleService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Dbj
 * @date 2023年05月10日  18:12
 */
@Repository
public class MultipleReportRepository {

    @Resource
    SUserService sUserService;
    @Resource
    SysRoleService sysRoleService;

    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    SysAreaService sysAreaService;

    public Map<String, Object> multiplePage(MultiplePageQueryParam param, int curPage, int pageSize) {
    StringBuilder sql =
        new StringBuilder(
            "select task.dt_id,task.task_num,task.task_name,task.task_district,task.create_user_name,task.report_id,"
                + " CASE WHEN report.check_status = '0' THEN '0'"
                + " WHEN report.check_status is null THEN '0' "
                 + " WHEN report.check_status='1' THEN '1'"
               + " WHEN report.check_status='2' THEN '2'"
                 + " else '3' end as taskStatus,report.upload_multiple_report_name,report.system_create_multiple_report_name from dzzh_task task left join dzzh_task_multiple_report report on task.report_id = report.dtmr_id ");
        StringBuilder whereSql = new StringBuilder(" where task.del_flag = '"+ YNEnum.N.toString() +"'");
        StringBuilder orderSql = new StringBuilder(" order by task.create_time desc");
        StringBuilder countSql = new StringBuilder(" select count(*) from dzzh_task task left join dzzh_task_multiple_report report on task.task_num = report.num  ");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;

        StringBuilder str = new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            str.append(roleMap.get("roleCode")).append(",");
        }
        String roles = str.toString();
        if (roles.contains(FxfzConstants.DZZH_COUN_GL_ROLE) || roles.contains(FxfzConstants.DZZH_COUN_YW_ROLE)) {
            //查看全部
        }else if (roles.contains(FxfzConstants.DZZH_PRI_GL_ROLE)) {
            String areaName = sysAreaService.getAreaNameByAreaId(sUser.getProvinceId());
            //省管理员查看自己省的
            whereSql.append(" and task.task_district like '%").append(areaName).append("%'");
        }else if (roles.contains(FxfzConstants.DZZH_PRI_YW_ROLE)) {
            //省业务人员查看自己创建的任务
            whereSql.append(" and task.create_user = '").append(PlatformSessionContext.getUserID()).append("'");
        }

        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        if (!PlatformObjectUtils.isEmpty(param.getNum())) {
            whereSql.append(" and task.task_num like '%").append(param.getNum()).append("%'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getTaskName())) {
            whereSql.append(" and task.task_name like '%").append(param.getTaskName()).append("%'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getTaskStatus())) {
            whereSql.append(" and report.check_status = '" + param.getTaskStatus() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProvince())) {
            if (PlatformObjectUtils.isNotEmpty(param.getCity())) {
                if (PlatformObjectUtils.isNotEmpty(param.getArea())) {
                    if(param.getCity().equals(param.getProvince())){
                        whereSql.append(" and (task.task_district like '%").append(param.getProvince()).append("-").append(param.getArea()).append("%' or task.task_district like '%").append(param.getProvince()).append(",%' or task.task_district like '%,").append(param.getProvince()).append("'  or task.task_district = '").append(param.getProvince()).append("' or " +
                                " task.task_district = '").append(param.getProvince()).append(param.getArea()).append("' or task.task_district like '%").append(param.getProvince()).append(",%' or task.task_district like '%,").append(param.getProvince()).append("' or task.task_district = '").append(param.getProvince()).append("')");
                    }else{
                        whereSql.append(" and (task.task_district like '%").append(param.getProvince()).append("-").append(param.getCity()).append("-").append(param.getArea()).append("%' or task.task_district like '%").append(param.getProvince()).append("-").append(param.getCity()).append(",%' or task.task_district like '%,").append(param.getProvince()).append("-").append(param.getCity()).append("' or task.task_district = '").append(param.getProvince()).append("-").append(param.getCity()).append("' or task.task_district like '%").append(param.getProvince()).append(",%' or task.task_district like '%,").append(param.getProvince()).append("' or task.task_district = '").append(param.getProvince()).append("'")
                                .append(" or task.task_district = '").append(param.getProvince()).append(param.getCity()).append(param.getArea()).append("'").append(" or task.task_district like '%").append(param.getProvince()).append(param.getCity()).append(",%' or task.task_district like '%,").append(param.getProvince()).append(param.getCity()).append("' or task.task_district = '").append(param.getProvince()).append(param.getCity()).append("' or task.task_district like '%,").append(param.getProvince()).append("' or task.task_district like '%").append(param.getProvince()).append(",%' or task.task_district = '").append(param.getProvince()).append("' )");
                    }
                } else {
                    if(param.getCity().equals(param.getProvince())){
                        //省市相同例如北京、上海、天津和重庆
                        whereSql.append(" and (task.task_district like '%").append(param.getProvince()).append("-").append("%' or task.task_district  like '%,").append(param.getProvince()).append("' or task.task_district  like '%").append(param.getProvince()).append(",%' or task.task_district = '").append(param.getProvince()).append("' or task.task_district like '%").append(param.getProvince()).append("%')");
                    }else{
                        whereSql.append(" and (task.task_district like '%").append(param.getProvince()).append("-").append(param.getCity()).append("%' or task.task_district like '%").append(param.getProvince()).append("-").append(param.getCity()).append(",%' or task.task_district = '").append(param.getProvince()).append("-").append(param.getCity()).append("' or task.task_district like '%").append(param.getProvince()).append(",%' or task.task_district like '%,").append(param.getProvince()).append("' or task.task_district = '").append(param.getProvince()).append("'")
                                .append(" or task.task_district like '%").append(param.getProvince()).append(param.getCity()).append("%' or task.task_district like '%").append(param.getProvince()).append(param.getCity()).append(",%' or task.task_district = '").append(param.getProvince()).append(param.getCity()).append("' or task.task_district like '%").append(param.getProvince()).append(",%' or task.task_district like '%,").append(param.getProvince()).append("' or task.task_district = '").append(param.getProvince()).append("')");
                    }
                }
            } else {
                whereSql.append(" and (task.task_district like '%").append(param.getProvince()).append(",%' or task.task_district = '").append(param.getProvince()).append("' or task.task_district LIKE '%,").append(param.getProvince()).append("' or task.task_district LIKE '%").append(param.getProvince()).append("%')");
            }

        }
//        if (PlatformObjectUtils.isNotEmpty(param.getProvince())) {
//            if (PlatformObjectUtils.isNotEmpty(param.getCity())) {
//                if (PlatformObjectUtils.isNotEmpty(param.getArea())) {
//                    if(param.getCity().equals(param.getProvince())){
//                        whereSql.append(" and (task.task_district = '").append(param.getProvince()).append("-").append(param.getArea()).append("' or task.task_district like '%").append(param.getProvince()).append("、%' or task.task_district like '%").append(param.getProvince()).append("-").append("%'  or task.task_district = '").append(param.getProvince()).append("')");
//                    }else{
//                        whereSql.append(" and (task.task_district like '%").append(param.getProvince()).append("-").append(param.getCity()).append("-").append(param.getArea()).append("%'").append(" or task.task_district like '%").append(param.getProvince()).append("-").append(param.getCity()).append("、%' or task.task_district = '").append(param.getProvince()).append("-").append(param.getCity()).append("' or task.task_district like '%").append(param.getProvince()).append("、%' or task.task_district = '").append(param.getProvince()).append("')");
//                    }
//                } else {
//                    if(param.getCity().equals(param.getProvince())){
//                        //省市相同例如北京、上海、天津和重庆
//                        whereSql.append(" and (task.task_district like '%").append(param.getProvince()).append("-").append("%' or task.task_district  like '%").append(param.getProvince()).append("、%' or task.task_district = '").append(param.getProvince()).append("')");
//                    }else{
//                        whereSql.append(" and (task.task_district like '%").append(param.getProvince()).append("-").append(param.getCity()).append("、%' or task.task_district = '").append(param.getProvince()).append("-").append(param.getCity()).append("' or task.task_district like '%").append(param.getProvince()).append("、%' or task.task_district = '").append(param.getProvince()).append("')");
//                    }
//                }
//            } else {
//                whereSql.append(" and (task.task_district like '%").append(param.getProvince()).append("、%' or task.task_district = '").append(param.getProvince()).append("' or task.task_district LIKE '%").append(param.getProvince()).append("-%')");
//            }
//
//        }
        String sqlWhole = sql.append(whereSql).append(orderSql).append(limitSql).toString();
        countSql.append(whereSql);
        List<MultipleReportVO> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(MultipleReportVO.class));
        Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    public MultipleReport getReportById(String reportId) {
        String sql = "select * from dzzh_task_multiple_report where dtmr_id='"+reportId+"'";
        List<MultipleReport> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(MultipleReport.class));
        return PlatformObjectUtils.isNotEmpty(query) && query.size()>0?query.get(0):null;
    }

    public void save(MultipleReport report) {
        String sql =
                "insert into dzzh_task_multiple_report(dtmr_id,"
                        + "system_create_multiple_report_name,"
                        + "disk_system_create_multiple_report_name,"
                        + "system_multiple_create_time,"
                        + "system_multiple_create_user_id,"
                        + "upload_multiple_report_name,"
                        + "disk_upload_multiple_report_name,"
                        + "upload_multiple_report_time,"
                        + "upload_multiple_user_id,"
                        + "report_save_path,"
                        + "check_status,num,district,task_name,evalute_user_name) values(:dtmrId, :systemCreateMultipleReportName, :diskSystemCreateMultipleReportName, :systemMultipleCreateTime, :systemMultipleCreateUserId, :uploadMultipleReportName, :diskUploadMultipleReportName , "
                        + ":uploadMultipleReportTime, :uploadMultipleUserId, :reportSavePath, :checkStatus,:num,:district,:taskName,:createUserName) ";
        SqlParameterSource source = new BeanPropertySqlParameterSource(report);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    public void updateUploadReport(MultipleReport report) {
        StringBuilder sql = new StringBuilder("update dzzh_task_multiple_report set ");
        if(PlatformObjectUtils.isNotEmpty(report.getUploadMultipleReportName())){
            sql.append("upload_multiple_report_name='").append(report.getUploadMultipleReportName()).append("',");
        }
        if(PlatformObjectUtils.isNotEmpty(report.getDiskUploadMultipleReportName())){
            sql.append("disk_upload_multiple_report_name='").append(report.getDiskUploadMultipleReportName()).append("',");
        }
        if(PlatformObjectUtils.isNotEmpty(report.getUploadMultipleUserId())){
            sql.append("upload_multiple_user_id='").append(report.getUploadMultipleUserId()).append("',");
        }
        sql.append("upload_multiple_report_time=now()");
        sql.append( "where dtmr_id='").append(report.getDtmrId()).append("'");
        jdbcTemplate.update(sql.toString());
    }

    public void submitCheak(String reportId, String submitUserId, String userName) {
        String sql = "update dzzh_task_multiple_report set check_status='1',submit_user_id='"+submitUserId+"',submit_time=now(),submit_user_name='"+userName+"' where dtmr_id='"+reportId+"'";
        jdbcTemplate.update(sql);
    }

    public void updateSystemCreateReport(MultipleReport report) {
        StringBuilder sql = new StringBuilder("update dzzh_task_multiple_report set ");
        if(PlatformObjectUtils.isNotEmpty(report.getSystemCreateMultipleReportName())){
            sql.append("system_create_multiple_report_name='").append(report.getSystemCreateMultipleReportName()).append("',");
        }
        if(PlatformObjectUtils.isNotEmpty(report.getDiskSystemCreateMultipleReportName())){
            sql.append("disk_system_create_multiple_report_name='").append(report.getDiskSystemCreateMultipleReportName()).append("',");
        }
        if(PlatformObjectUtils.isNotEmpty(report.getSystemMultipleCreateUserId())){
            sql.append("system_multiple_create_user_id='").append(report.getSystemMultipleCreateUserId()).append("',");
        }
        sql.append("system_multiple_create_time=now()");
        sql.append( " where dtmr_id='").append(report.getDtmrId()).append("'");
        jdbcTemplate.update(sql.toString());
    }

    public void reportAudit(Report report, SUser sUser) {
        StringBuilder sql = new StringBuilder("update dzzh_task_multiple_report set check_status='"+report.getCheckStatus()+"'");
        if("2".equals(report.getCheckStatus())){
            sql.append(",reject_advice='").append(report.getRejectAdvice()).append("'");
        }
        sql.append(",reject_file='").append(PlatformObjectUtils.isNotEmpty(report.getRejectFile())?report.getRejectFile():"").append("'");
        sql.append(",reject_real_file='").append(PlatformObjectUtils.isNotEmpty(report.getRejectRealFile())?report.getRejectRealFile():"").append("'");
        sql.append(",audit_user='").append(sUser.getUserId()).append("'");
        sql.append(",audit_user_name='").append(sUser.getUserName()).append("'");
        sql.append(",audit_time=now()");
        sql.append(" where dtmr_id='").append(report.getReportId()).append("'");
        jdbcTemplate.update(sql.toString());
    }

    public void uploadAttachment(String fileName,String realFileName, String reportId) {
        StringBuilder sql = new StringBuilder("update dzzh_task_multiple_report set reject_file='"+fileName+"',reject_real_file='"+realFileName+"' where dtmr_id='").append(reportId).append("'");
        jdbcTemplate.update(sql.toString());
    }
}
